﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DVSProject.Models;
using System.Web.Mvc;
using System.Data;

namespace DVSProject.Lib
{
    public class MachineRepo
    {
        public const string m_msg_head = "Machine";

        public IEnumerable<MachineModels> GetMachine()
        {
            List<MachineModels> Machines = new List<MachineModels>();
            string SQLCommand = @"select TBMachine.*,TBModel.NameTh AS Model_NameTh from TBMachine LEFT JOIN TBModel
                                   ON TBMachine.ModelId = TBModel.ModelId WHERE TBMachine.Status <> 'Delete'";
            using (var db = new DBHelper())
            {
                try
                {
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        MachineModels Machine = new MachineModels();
                        Machine.SerialNo = (string)reader["SerialNo"];
                        Machine.ModelId = (int)reader["ModelId"];
                        Machine.Brand = (string)reader["Brand"];
                        Machine.Model_NameTh = (string)reader["Model_NameTh"];
                        Machine.NameTh = (string)reader["NameTh"];
                        Machine.NameEng = (string)reader["NameEng"];
                        Machine.max_meter_no = (reader["max_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["max_meter_no"]) : 0;
                        Machine.start_meter_no =(reader["start_meter_no"]!=DBNull.Value)? SF.GetDouble(reader["start_meter_no"]):0;
                        Machine.last_meter_no = (reader["last_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["last_meter_no"]) : 0;   //SF.GetDouble(reader["last_meter_no"]);
                        Machine.Status = (string)reader["Status"];
                        Machine.CreateBy = (string)reader["CreateBy"];
                        Machine.CreateOn = (DateTime)reader["CreateOn"];
                        Machine.UpdateBy = (string)reader["UpdateBy"];
                        Machine.UpdateOn = (DateTime)reader["UpdateOn"];

                        //ModelRepo _ModelRepo = new ModelRepo();
                        //IEnumerable<ModelModels> _Models = _ModelRepo.GetModel();

                        ////int selectedModelId = 1;
                        //IEnumerable<SelectListItem> selectlist = from s in _Models
                        //                                         select new SelectListItem
                        //                                         {
                        //                                            // Selected = (s.Id == selectedModelId),
                        //                                             Text = s.NameEng,
                        //                                             Value = s.Id.ToString()
                        //                                         };

                        //Machine.ModelMacs = _Models;
                        //Machine.ModelMacSelect = selectlist;

                        Machines.Add(Machine);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return Machines.ToList();
        }
        public IEnumerable<MachineModels> GetMachineForChange()
        {
            List<MachineModels> Machines = new List<MachineModels>();
            string SQLCommand = @"select TBMachine.*,TBModel.NameTh AS Model_NameTh from TBMachine LEFT JOIN TBModel
                                   ON TBMachine.ModelId = TBModel.ModelId ";
            using (var db = new DBHelper())
            {
                try
                {
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        MachineModels Machine = new MachineModels();
                        Machine.SerialNo = (string)reader["SerialNo"];
                        Machine.ModelId = (int)reader["ModelId"];
                        Machine.Brand = (string)reader["Brand"];
                        Machine.Model_NameTh = (string)reader["Model_NameTh"];
                        Machine.NameTh = (string)reader["NameTh"];
                        Machine.NameEng = (string)reader["NameEng"];
                        Machine.max_meter_no = (reader["max_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["max_meter_no"]) : 0;
                        Machine.start_meter_no = (reader["start_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["start_meter_no"]) : 0;
                        Machine.last_meter_no = (reader["last_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["last_meter_no"]) : 0;   //SF.GetDouble(reader["last_meter_no"]);
                        Machine.Status = (string)reader["Status"];
                        Machine.CreateBy = (string)reader["CreateBy"];
                        Machine.CreateOn = (DateTime)reader["CreateOn"];
                        Machine.UpdateBy = (string)reader["UpdateBy"];
                        Machine.UpdateOn = (DateTime)reader["UpdateOn"];

                        //ModelRepo _ModelRepo = new ModelRepo();
                        //IEnumerable<ModelModels> _Models = _ModelRepo.GetModel();

                        ////int selectedModelId = 1;
                        //IEnumerable<SelectListItem> selectlist = from s in _Models
                        //                                         select new SelectListItem
                        //                                         {
                        //                                            // Selected = (s.Id == selectedModelId),
                        //                                             Text = s.NameEng,
                        //                                             Value = s.Id.ToString()
                        //                                         };

                        //Machine.ModelMacs = _Models;
                        //Machine.ModelMacSelect = selectlist;

                        Machines.Add(Machine);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return Machines.ToList();
        }

        public IEnumerable<MachineModels> GetMachineForNewSetup()
        {
            List<MachineModels> Machines = new List<MachineModels>();
            string SQLCommand = @"select TBMachine.*,TBModel.NameTh AS Model_NameTh 
                                   from TBMachine LEFT JOIN TBModel ON TBMachine.ModelId = TBModel.ModelId 
                                   WHERE TBMachine.Status <> 'Delete' and SerialNo NOT IN (SELECT SerialNo FROM TBLocationMachineSet WHERE Status <> 'Delete')";
            using (var db = new DBHelper())
            {
                try
                {
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        MachineModels Machine = new MachineModels();
                        Machine.SerialNo = (string)reader["SerialNo"];
                        Machine.ModelId = (int)reader["ModelId"];
                        Machine.Brand = (string)reader["Brand"];
                        Machine.Model_NameTh = (string)reader["Model_NameTh"];
                        Machine.NameTh = (string)reader["NameTh"];
                        Machine.NameEng = (string)reader["NameEng"];
                        Machine.max_meter_no = (reader["max_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["max_meter_no"]) : 0;
                        Machine.start_meter_no = (reader["start_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["start_meter_no"]) : 0;
                        Machine.last_meter_no = (reader["last_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["last_meter_no"]) : 0;   //SF.GetDouble(reader["last_meter_no"]);
                        Machine.Status = (string)reader["Status"];
                        Machine.CreateBy = (string)reader["CreateBy"];
                        Machine.CreateOn = (DateTime)reader["CreateOn"];
                        Machine.UpdateBy = (string)reader["UpdateBy"];
                        Machine.UpdateOn = (DateTime)reader["UpdateOn"];

                        //ModelRepo _ModelRepo = new ModelRepo();
                        //IEnumerable<ModelModels> _Models = _ModelRepo.GetModel();

                        ////int selectedModelId = 1;
                        //IEnumerable<SelectListItem> selectlist = from s in _Models
                        //                                         select new SelectListItem
                        //                                         {
                        //                                            // Selected = (s.Id == selectedModelId),
                        //                                             Text = s.NameEng,
                        //                                             Value = s.Id.ToString()
                        //                                         };

                        //Machine.ModelMacs = _Models;
                        //Machine.ModelMacSelect = selectlist;

                        Machines.Add(Machine);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return Machines.ToList();
        }
        public MachineModels GetMachineById(string SerialNo)
        {
            MachineModels Machine = new MachineModels();
            string SQLCommand = @"select * from TBMachine where SerialNo=@SerialNo";

            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@SerialNo", SerialNo);
                    var reader = db.ExecuteReader(SQLCommand);
                    if (reader.Read())
                    {
                        Machine.SerialNo = (string)reader["SerialNo"];
                        Machine.ModelId = (int)reader["ModelId"];
                        Machine.Brand = (string)reader["Brand"];
                        Machine.NameTh = (string)reader["NameTh"];
                        Machine.NameEng = (string)reader["NameEng"];
                        Machine.max_meter_no = (reader["max_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["max_meter_no"]) : 0;
                        Machine.start_meter_no = (reader["start_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["start_meter_no"]) : 0;
                        Machine.last_meter_no = (reader["last_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["last_meter_no"]) : 0;
                        Machine.Status = (string)reader["Status"];
                        Machine.CreateBy = (string)reader["CreateBy"];
                        Machine.CreateOn = (DateTime)reader["CreateOn"];
                        Machine.UpdateBy = (string)reader["UpdateBy"];
                        Machine.UpdateOn = (DateTime)reader["UpdateOn"];
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
                }
            }
            return Machine;
        }

        public bool Create(MachineModels Machine)
        {
            bool result = true;
            string SQLCommand = @"INSERT into TBMachine 
                                    values (@SerialNo,@ModelId,@Brand,@NameTh,@NameEng,@max_meter_no,@start_meter_no,@last_meter_no,@Status,@UserId,
                                    GETDATE(),@UserId,GETDATE())";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@SerialNo", Machine.SerialNo);
                    db.AddParameter("@ModelId", Machine.ModelId);
                    db.AddParameter("@Brand", Machine.Brand);
                    db.AddParameter("@NameTh", Machine.NameTh);
                    db.AddParameter("@NameEng", Machine.NameEng);
                    db.AddParameter("@max_meter_no", Machine.max_meter_no);
                    db.AddParameter("@start_meter_no", Machine.start_meter_no);
                    db.AddParameter("@last_meter_no", Machine.last_meter_no);
                    db.AddParameter("@Status", "Active");
                    db.AddParameter("@UserID", "900153");

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Edit(MachineModels Machine)
        {
            bool result = true;
            string SQLCommand = @"Update TBMachine 
                                    Set ModelId=@ModelId,
                                        SerialNo=@SerialNo,
                                        Brand=@Brand,
                                        NameTh=@NameTh,
                                        NameEng=@NameEng,
                                        max_meter_no =@max_meter_no,
                                        start_meter_no =@start_meter_no,
                                        last_meter_no =@last_meter_no,
                                        UpdateBy=@UserId,UpdateOn=getdate()
                                    where SerialNo=@SerialNo";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@SerialNo", Machine.SerialNo);
                    db.AddParameter("@ModelId", Machine.ModelId);
                    db.AddParameter("@Brand", Machine.Brand);
                    db.AddParameter("@NameTh", Machine.NameTh);
                    db.AddParameter("@NameEng", Machine.NameEng);
                    db.AddParameter("@max_meter_no", Machine.max_meter_no);
                    db.AddParameter("@start_meter_no", Machine.start_meter_no);
                    db.AddParameter("@last_meter_no", Machine.last_meter_no);
                    db.AddParameter("@UserID", Machine.UpdateBy  );

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Delete(MachineModels Machine)
        {
            bool result = true;
            string SQLCommand = @"Update TBMachine 
                                    Set Status=@Status,UpdateBy=@UserId,UpdateOn=getdate()
                                    where SerialNo=@SerialNo";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@SerialNo", Machine.SerialNo);
                    db.AddParameter("@Status", "Delete");
                    db.AddParameter("@UserID", Machine.UpdateBy);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0005", ex));
                    return false;
                }
            }
            return result;
        }

        public bool UpDateLastMeterNo(MachineModels Machine)
        {
            bool result = true;
            string SQLCommand = @"Update TBMachine 
                                    Set last_meter_no=@last_meter_no,UpdateBy=@UserId,UpdateOn=getdate()
                                    where SerialNo=@SerialNo";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@SerialNo", Machine.SerialNo);
                    db.AddParameter("@last_meter_no", Machine.last_meter_no);
                    db.AddParameter("@UserID", "900153");

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0005", ex));
                    return false;
                }
            }
            return result;
        }
        public bool CheckDub(string SerialNo)
        {
            bool result;

            DataSet SummaryMeters = new DataSet();
            string SQLCommand = @" Select SerialNo From TBMachine  Where SerialNo=@SerialNo";

            //DECLARE @CODE_NO1	varchar(4)
            //                    DECLARE @LASTNO		int
            //                    SET @LASTNO =(select isnull(max(right(isnull(LocationSetCode ,0),3)),0)+1   from TBLocationSet WHERE LocationSetCode <> 'NS02' )
            //                    SET @CODE_NO1='M' + STUFF('000', len('000')-(len(@LASTNO)-1), len(@LASTNO),@LASTNO)
            using (var db = new DBHelper())
            {
                try
                {

                    db.AddParameter("@SerialNo", SerialNo);
                    SummaryMeters = db.ExecuteDataSet(SQLCommand);
                    if (SummaryMeters.Tables[0].Rows.Count > 0)
                    {
                        result = true;
                    }
                    else { result = false; }
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return true;
                }
            }
            return result;
        }
    }
}